package com.threeti.mecool.core.application.report;

import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.EAST_AREA_CODE;
import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.NORTH_AREA_CODE;
import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.SOUTH_AREA_CODE;
import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.WEST_AREA_CODE;
import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.customAreaNameCodeRegistry;
import static com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry.customAreaRegistry;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.hibernate.dialect.Oracle10gDialect;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.threeti.mecool.core.application.acl.UserService;
import com.threeti.mecool.core.application.basicdata.BasicDataService;
import com.threeti.mecool.core.application.project.ProjectService;
import com.threeti.mecool.core.domain.model.acl.OrgnizationEmployee;
import com.threeti.mecool.core.domain.model.acl.PermissionRole;
import com.threeti.mecool.core.domain.model.acl.User;
import com.threeti.mecool.core.domain.model.basicdata.AdminDivision;
import com.threeti.mecool.core.domain.model.basicdata.AdminDivisionLevelDeep;
import com.threeti.mecool.core.domain.model.basicdata.biz.ChannelSync;
import com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry;
import com.threeti.mecool.core.domain.model.plan.PlanJobResult;
import com.threeti.mecool.core.domain.model.plan.WorkFlowConst;
import com.threeti.mecool.core.domain.model.project.Project;
import com.threeti.mecool.core.domain.model.sellinto.SellIntoStatus;
import com.threeti.mecool.core.infrastructure.persistence.project.ProjectEmployeeRepository;
import com.threeti.mecool.core.infrastructure.persistence.project.ProjectRepository;
import com.threeti.mecool.framework.util.Lists;
import com.threeti.mecool.framework.util.Maps;
import com.threeti.mecool.framework.util.Sets;
import com.threeti.mecool.legacy.domain.model.basicdata.AdminDivisionLegacy;
import com.threeti.mecool.legacy.infrastructure.persistence.AdminDivisionLegacyRepository;
import com.threeti.mecool.shared.util.AbstractTreeNode;

@Service
public class ReportServiceImpl implements ReportService {

  public static final Logger logger = LoggerFactory.getLogger(ReportServiceImpl.class);
  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Autowired
  private BasicDataService basicService;

  @Autowired
  private AdminDivisionLegacyRepository adminDivLegRepo;

//  @Autowired
//  private ReportCacheService rptCacheService;

  @Autowired
  private UserService userService;

  @Autowired
  private ProjectService projectService;

  @Autowired
  private ProjectRepository projRepo;

  @Autowired
  ProjectEmployeeRepository projectEmployeeRepository;

  private static final String QUERY_DM_V_TPR_SUM_BY_PROVINCE =
			" SELECT PL.CITY_AD_NAME, PL.CITY_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
			" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME CITY_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
			" WHERE JR.CHANNEL = CS.ID AND " + //--关联任务和通路
			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
  	  " ?1 "+
  	  " ?3 "+
			" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME " +
			" HAVING CS.CHANNEL_PROVINCE_AD_CODE = ?5) PL " +//--计划任务数统计
			" LEFT JOIN " +
			" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS" +
			" WHERE JR.CHANNEL = CS.ID AND " + //--关联任务和通路
			" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND "+//--暂时把未经审核或审核成功的任务都视为成功任务
			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND " + //--过滤出状态为完成的任务
			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
  	  " ?2 "+
  	  " ?4 "+
			" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE,CS.CHANNEL_CITY_AD_CODE " +
			" HAVING CS.CHANNEL_PROVINCE_AD_CODE = ?6) AC " +//--实际任务完成数统计
			" ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE AND PL.CITY_AD_CODE = AC.CITY_AD_CODE";

  private static final String QUERY_DM_V_TPR_SUM_BY_PROVINCE_ALL =
			" SELECT PL.PROVINCE_AD_NAME, PL.PROVINCE_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
			" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
			" WHERE JR.CHANNEL = CS.ID AND "+//--关联任务和通路
			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
  	  " ?1 "+
  	  " ?3 "+
			" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME) PL "+// --计划任务数统计
			" LEFT JOIN " +
			" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS" +
			" WHERE " +
			" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND " +//--暂时把未经审核或审核成功的任务都视为成功任务
  	  " JR.CHANNEL = CS.ID AND "+//--关联任务和通路
			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND " + //--过滤出状态为完成的任务
			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
  	  " ?2 "+
  	  " ?4 "+
			" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE) AC " +//--实际任务完成数统计
			" ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE " +//--关联计划和实际
			" ORDER BY PL.PROVINCE_AD_CODE";

    /**
     * 按任务员工所分配地区汇总省份数据 md_emp.ad_id
     */
  public static final String QUERY_TASK_PERFORMANCE_RATE_SUMMARY_BY_PROVINCE_ALL =
			" SELECT PL.PROVINCE_AD_NAME, PL.PROVINCE_AD_CODE, NVL(PL.PLANNED_QUANTITY, 0) PLANNED_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
            " (select substr(ad.ad_code, 0, 2) || '000000' PROVINCE_AD_CODE, nvl(province_ad_name, nvl(city_ad_name, ad_name)) PROVINCE_AD_NAME, COUNT(*) PLANNED_QUANTITY " +
            " from dm_wf_plan_job_result jr, md_emp emp, md_admin_div ad " +
            " where emp.emp_pk=jr.emp_id and ad.ad_id=emp.ad_id " +
  	  " ?1 "+
  	  " ?3 "+
            " group by subStr(ad.ad_code, 0, 2), nvl(province_ad_name, nvl(city_ad_name, ad_name)) ) PL " +
			" LEFT JOIN " +
            " (select substr(ad.ad_code, 0, 2) || '000000' PROVINCE_AD_CODE, count(*) ACTUAL_QUANTITY " +
            " from dm_wf_plan_job_result jr, md_emp emp, md_admin_div ad " +
            " where emp.emp_pk=jr.emp_id and ad.ad_id=emp.ad_id " +
            " and jr.job_status='" + PlanJobResult.JOB_STATUS_FINISHED + "' " +
  	  " ?2 "+
  	  " ?4 "+
			" group by substr(ad.ad_code, 0, 2) ) AC " +
			" ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE " +
			" ORDER BY PL.PROVINCE_AD_CODE";

    /**
     * 按任务员工所分配地区汇总城市数据 md_emp.ad_id
     */
  private static final String QUERY_TASK_PERFORMANCE_RATE_SUMMARY_BY_PROVINCE =
            " SELECT PL.CITY_AD_NAME, PL.CITY_AD_CODE, NVL(PL.PLANNED_QUANTITY, 0) PLANNED_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
            " (select substr(ad.ad_code, 0, 2) || '000000' PROVINCE_AD_CODE, " +
            " nvl(province_ad_name, nvl(city_ad_name, ad_name)) PROVINCE_AD_NAME, " +
            " substr(ad.ad_code, 2, 4) || '0000' CITY_AD_CODE, " +
            " decode(deep, 4, substr(city_ad_name, 0, length(city_ad_name)-3), 3, city_ad_name, ad_name) CITY_AD_NAME, " +
            " count(*) PLANNED_QUANTITY " +
            " from dm_wf_plan_job_result jr, md_emp emp, md_admin_div ad " +
            " where emp.emp_pk=jr.emp_id and ad.ad_id=emp.ad_id " +
            " and substr(ad.ad_code, 0, 2) || '000000' = ?5 " +
      " ?1 "+
      " ?3 "+
            " group by substr(ad.ad_code, 0, 2), " +
            " nvl(province_ad_name, nvl(city_ad_name, ad_name)), " +
            " substr(ad.ad_code, 2, 4), " +
            " decode(deep, 4, substr(city_ad_name, 0, length(city_ad_name)-3), 3, city_ad_name, ad_name) " +
            " ) PL " +
            " LEFT JOIN " +
            " (select substr(ad.ad_code, 0, 2) || '000000' PROVINCE_AD_CODE, " +
            " substr(ad.ad_code, 2, 4) || '0000' CITY_AD_CODE, " +
            " count(*) ACTUAL_QUANTITY " +
            " from dm_wf_plan_job_result jr, md_emp emp, md_admin_div ad " +
            " where emp.emp_pk=jr.emp_id and ad.ad_id=emp.ad_id " +
            " and jr.job_status='" + PlanJobResult.JOB_STATUS_FINISHED + "' " +
            " and substr(ad.ad_code, 0, 2) || '000000' = ?6 " +
      " ?2 "+
      " ?4 "+
            " group by substr(ad.ad_code, 0, 2), " +
            " substr(ad.ad_code, 2, 4) " +
            " ) AC " +
            " ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE AND PL.CITY_AD_CODE = AC.CITY_AD_CODE";


  @Override
  public StoreOpeningRate storeOpeningRateSummaryByCountry(StoreOpeningRateReportCriteria criteria) {

    List<StoreOpeningRate> sors = storeOpeningRateSummaryByProvinceALL(criteria);
    int totalPlannedQuantity = 0;
    int totalActualQuantity = 0;
    for (StoreOpeningRate sor : sors) {
      totalPlannedQuantity += sor.getPlannedQuantityOfStoreOpening();
      totalActualQuantity += sor.getActualQuantityOfStoreOpening();
    }
    return new StoreOpeningRate("全国", "", totalPlannedQuantity, totalActualQuantity);
  }

  @Override
  public List<StoreOpeningRate> storeOpeningRateSummaryByCustomAreas(StoreOpeningRateReportCriteria criteria) {
    List<StoreOpeningRate> allSors = storeOpeningRateSummaryByProvinceALL(criteria);
    Map<String, List<String>> areaReg = customAreaRegistry();
    Map<String, String[]> areaNameCodeReg = customAreaNameCodeRegistry();
    List<StoreOpeningRate> rtnSors = Lists.newArrayList();
    rtnSors.add(calculateStoreOpeningRateByCustomArea(EAST_AREA_CODE, allSors, areaReg, areaNameCodeReg));
    rtnSors.add(calculateStoreOpeningRateByCustomArea(SOUTH_AREA_CODE, allSors, areaReg, areaNameCodeReg));
    rtnSors.add(calculateStoreOpeningRateByCustomArea(WEST_AREA_CODE, allSors, areaReg, areaNameCodeReg));
    rtnSors.add(calculateStoreOpeningRateByCustomArea(NORTH_AREA_CODE, allSors, areaReg, areaNameCodeReg));
    return rtnSors;
  }

  @Override
  public List<StoreOpeningRate> storeOpeningRateSummaryByCustomArea(String customAreaCode, StoreOpeningRateReportCriteria criteria) {
    List<StoreOpeningRate> allSors = storeOpeningRateSummaryByProvinceALL(criteria);
    List<StoreOpeningRate> rtnSors = Lists.newArrayList();
    Map<String, List<String>> areaReg = customAreaRegistry();
    List<String> targetProvinceCodes = areaReg.get(customAreaCode);

    for (StoreOpeningRate sor : allSors) {
      if (targetProvinceCodes.contains(sor.getAdminDivAreaCode())) {
        rtnSors.add(new StoreOpeningRate(sor.getAdminDivAreaName(), sor.getAdminDivAreaCode(), sor.getPlannedQuantityOfStoreOpening(), sor.getActualQuantityOfStoreOpening()));
      }
    }
    return rtnSors;
  }

  public List<StoreOpeningRate> storeOpeningRateSummaryByProvinceALL(StoreOpeningRateReportCriteria criteria) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedRefreshWithSorCheck()) {
//  		rptCacheService.doRefresh("Profiling for storeOpeningRateSummaryByProvinceALL report cache refresh...", "DM_MV_SOR_SUM_BY_PROVINCE_ALL");
//  	}
    //String sql = "select * from DM_V_SOR_SUM_BY_PROVINCE_ALL";
  	//<-
    String sql =
    		" SELECT PL.PROVINCE_AD_NAME, PL.PROVINCE_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
    	  " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
    	  " FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
    	  " WHERE PS.PROJECT = PJ.ID AND "+// --关联门店和项目
    	  " PJ.STATUS = 1 AND "+//--指定项目状态(Active，未完成即进行中状态)
    	  " PS.CHANNEL = CS.ID AND " +//--关联门店和通路
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?1 "+
    	  " ?3 "+
    	  " ?5 "+
    	  " ?7 "+
    	  " ?9 "+
    	  " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME) PL" +//--计划开店统计
    	  " LEFT JOIN "+
    	  " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
    	  " FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    	  " WHERE JR.JOB_CODE = '"+WorkFlowConst.CODE_WF_JOB_TYPE_STORE_OPENING+"' AND " +//过滤出开店任务
    	  " JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND "+//--过滤出状态为完成的任务
    	  " (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND " +//--暂时把未经审核或审核成功的任务都视为成功开店任务
    	  " JR.CHANNEL = CS.ID AND " +//--关联任务和通路
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?2 "+
    	  " ?4 "+
    	  " ?6 "+
    	  " ?8 "+
    	  " ?0 "+
    	  " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE) AC " +//--实际开店统计
    	  " ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE "+//--关联计划和实际
    	  " ORDER BY PL.PROVINCE_AD_CODE";

    Map<String, StoreOpeningRate> recordsExist = generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(sql,criteria);

    List<AdminDivision> provinces = basicService.findAdminDivisionsByLevelDeep(AdminDivisionLevelDeep.PROVINCE);
    List<StoreOpeningRate> rtnRates = Lists.newArrayList();
    for (AdminDivision adminDiv : provinces) {
      StoreOpeningRate sor = null;
      if ((sor = recordsExist.get(adminDiv.getId())) != null) {
        rtnRates.add(sor);
      } else {
        rtnRates.add(new StoreOpeningRate(adminDiv.getName(), adminDiv.getId(), 0, 0));
      }
    }

    return rtnRates;
  }

  @Override
  public List<StoreOpeningRate> storeOpeningRateSummaryByProvince(String provinceAdCode, StoreOpeningRateReportCriteria criteria) {
//jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedRefreshWithSorCheck()) {
//  		rptCacheService.doRefresh("Profiling for storeOpeningRateSummaryByProvince(String provinceAdCode) report cache refresh...", "DM_MV_SOR_SUM_BY_PROVINCE");
//  	}
//    String sql = "select * from DM_V_SOR_SUM_BY_PROVINCE where " +
//    		"P_VIEW_VCHAR2_PARAM.SET_PARAM('"+provinceAdCode+"')='"+provinceAdCode+"'";//jay:P_VIEW_VCHAR2_PARAM乃预定义的oracle package，以实现参数化视图
//<-

    String sql =
    		" SELECT PL.CHANNEL_CITY_AD_NAME, PL.CHANNEL_CITY_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
    		" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
    		" FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
    		" WHERE PS.PROJECT = PJ.ID AND " + //--关联门店和项目
    		" PJ.STATUS = 1 AND " +//--指定项目状态(Active，未完成即进行中状态)
    		" PS.CHANNEL = CS.ID AND " +//--关联门店和通路
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?1 "+
    	  " ?3 "+
    	  " ?5 "+
    	  " ?7 "+
    	  " ?9 "+
    		" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME " +
    		" HAVING CS.CHANNEL_PROVINCE_AD_CODE = '"+provinceAdCode+"') PL " +
    		" LEFT JOIN " +
    		" (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
    		" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    	  " WHERE JR.JOB_CODE = '"+WorkFlowConst.CODE_WF_JOB_TYPE_STORE_OPENING+"' AND " +//过滤出开店任务
    	  " JR.CHANNEL = CS.ID AND "+//--关联任务和通路
  			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND " + //--过滤出状态为完成的任务
    		" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND " +//--暂时把未经审核或审核成功的任务都视为成功开店任务
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?2 "+
    	  " ?4 "+
    	  " ?6 "+
    	  " ?8 "+
    	  " ?0 "+
    		" GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_CITY_AD_CODE " +
    		" HAVING CS.CHANNEL_PROVINCE_AD_CODE = '"+provinceAdCode+"') AC " +
    		" ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE AND PL.CHANNEL_CITY_AD_CODE = AC.CITY_AD_CODE";

    Map<String, StoreOpeningRate> recordsExist = generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(sql,criteria);

    AdminDivisionLegacy province = adminDivLegRepo.findOne(provinceAdCode);//fetch name only

    List<AdminDivision> cities = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(province.getName(), provinceAdCode, AdminDivisionLevelDeep.CITY);

    List<StoreOpeningRate> rtnRates = Lists.newArrayList();
    for (AdminDivision city : cities) {
      expandTree(city, rtnRates, recordsExist);
    }

    return rtnRates;
  }

  @Override
  public List<StoreOpeningRate> storeOpeningRateSummaryByCity(String cityAdCode, StoreOpeningRateReportCriteria criteria) {
  //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedRefreshWithSorCheck()) {
//  		rptCacheService.doRefresh("Profiling for storeOpeningRateSummaryByCity(String cityAdCode) report cache refresh...", "DM_MV_SOR_SUM_BY_CITY");
//  	}
//    String sql = "select * from DM_V_SOR_SUM_BY_CITY where " +
//        "P_VIEW_VCHAR2_PARAM.SET_PARAM('"+cityAdCode+"')='"+cityAdCode+"'";//jay:P_VIEW_VCHAR2_PARAM乃预定义的oracle package，以实现参数化视图
  //<-
  	String sql =
  			" SELECT PL.CHANNEL_AD_NAME, PL.CHANNEL_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
  			" (SELECT CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
  			" FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
  			" WHERE PS.PROJECT = PJ.ID AND " + //--关联门店和项目
  			" PJ.STATUS = 1 AND " +//--指定项目状态(Active，未完成即进行中状态)
    		" PS.CHANNEL = CS.ID AND " +//--关联门店和通路
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?1 "+
    	  " ?3 "+
    	  " ?5 "+
    	  " ?7 "+
    	  " ?9 "+
  			" GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME " +
  			" HAVING CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"') PL "+ //--计划开店统计
  			" LEFT JOIN " +
  			" (SELECT CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, CS.CHANNEL_AD_CODE AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
  			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    	  " WHERE JR.JOB_CODE = '"+WorkFlowConst.CODE_WF_JOB_TYPE_STORE_OPENING+"' AND " +//过滤出开店任务
    	  " JR.CHANNEL = CS.ID AND "+//--关联任务和通路
  			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND " + //--过滤出状态为完成的任务
  			" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND " +//--暂时把未经审核或审核成功的任务都视为成功开店任务
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?2 "+
    	  " ?4 "+
    	  " ?6 "+
    	  " ?8 "+
    	  " ?0 "+
  			" GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_AD_CODE " +
  			" HAVING CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"') AC " +
  			" ON PL.CHANNEL_CITY_AD_CODE = AC.CITY_AD_CODE AND PL.CHANNEL_AD_CODE = AC.AD_CODE";

    Map<String, StoreOpeningRate> recordsExist = generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(sql,criteria);

    List<AdminDivision> cityAreas = Lists.newArrayList();
    boolean isCityAreaSpecial = false;
    //trying find special city area and expand
    AdminDivisionLegacy city = adminDivLegRepo.findOne(cityAdCode);//fetch name only
    List<AdminDivision> tryingSpecialCityAreas = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(city.getName(), cityAdCode, AdminDivisionLevelDeep.CITY);
    List<StoreOpeningRate> tryingRates = Lists.newArrayList();
    for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
      expandTree(tryingSpecialCityArea, tryingRates, recordsExist);
    }
    if (CollectionUtils.isEmpty(tryingRates)) {
      //非直辖市(如南京市(江苏省) etc.)处理:->
      tryingSpecialCityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);

      for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
        expandTree(tryingSpecialCityArea, tryingRates, recordsExist);
      }

      for (StoreOpeningRate tryingRate : tryingRates) {
        String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //->
    } else {
      //直辖市(如上海、北京、天津 etc.)处理:->
      for (StoreOpeningRate tryingRate : tryingRates) {
        String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 保险起见，需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //->
    }

    //正常情况->
    if (!isCityAreaSpecial) {
      cityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
    }

    List<StoreOpeningRate> rtnRates = Lists.newArrayList();
    for (AdminDivision area : cityAreas) {
      expandTree(area, rtnRates, recordsExist);
    }
    //<-

    //在针对混合数据的场景(既有市辖区、又有直接区县，这两个不同维度数据合并在一起作为流入数据的情况)下，需要去除特殊数据
    if (isCityAreaSpecial) {
      List<StoreOpeningRate> ratesNeedsAppending = Lists.newArrayList();
      List<AdminDivision> cityAreasWithSpecialDataMixed = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
      for (AdminDivision area : cityAreasWithSpecialDataMixed) {
        expandTree(area, ratesNeedsAppending, recordsExist);
      }
      for (ListIterator<StoreOpeningRate> iterator = ratesNeedsAppending.listIterator(); iterator.hasNext();) {
        StoreOpeningRate sorNeedsAppending = iterator.next();
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(sorNeedsAppending.getAdminDivAreaName())) {
          iterator.remove();
        }
      }
      //和首轮处理过的数据进行合并
      rtnRates.addAll(ratesNeedsAppending);
    }

    return rtnRates;
  }

  @Override
  public TaskPerformanceRate taskPerformanceRateSummaryByCountry() {

    List<TaskPerformanceRate> tprs = taskPerformanceRateSummaryByProvinceALL(Maps.newHashMap());
    int totalPlannedQuantity = 0;
    int totalActualQuantity = 0;
    for (TaskPerformanceRate tpr : tprs) {
      totalPlannedQuantity += tpr.getPlannedQuantityOfTask();
      totalActualQuantity += tpr.getActualQuantityOfTask();
    }
    return new TaskPerformanceRate("全国", "", totalPlannedQuantity, totalActualQuantity);
  }

  @Override
  public List<TaskPerformanceRate> taskPerformanceRateSummaryByCustomAreas() {
    List<TaskPerformanceRate> allTprs = taskPerformanceRateSummaryByProvinceALL(Maps.newHashMap());
    Map<String, List<String>> areaReg = customAreaRegistry();
    Map<String, String[]> areaNameCodeReg = customAreaNameCodeRegistry();
    List<TaskPerformanceRate> rtnTprs = Lists.newArrayList();
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(EAST_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(SOUTH_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(WEST_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(NORTH_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    return rtnTprs;
  }

  @Override
  public List<TaskPerformanceRate> taskPerformanceRateSummaryByCustomArea(String customAreaCode) {
    List<TaskPerformanceRate> allTprs = taskPerformanceRateSummaryByProvinceALL(Maps.newHashMap());
    List<TaskPerformanceRate> rtnTprs = Lists.newArrayList();
    Map<String, List<String>> areaReg = customAreaRegistry();
    List<String> targetProvinceCodes = areaReg.get(customAreaCode);

    for (TaskPerformanceRate tpr : allTprs) {
      if (targetProvinceCodes.contains(tpr.getAdminDivAreaCode())) {
        rtnTprs.add(new TaskPerformanceRate(tpr.getAdminDivAreaName(), tpr.getAdminDivAreaCode(), tpr.getPlannedQuantityOfTask(), tpr.getActualQuantityOfTask()));
      }
    }
    return rtnTprs;
  }

  @SuppressWarnings({ "rawtypes", "unchecked" })
  public List<TaskPerformanceRate> taskPerformanceRateSummaryByProvinceALL(Map paramMap) {
  //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for taskPerformanceRateSummaryByProvinceALL report cache refresh...", "DM_MV_TPR_SUM_BY_PROVINCE_ALL");
//  	}
//  	String sql = "select * from DM_V_TPR_SUM_BY_PROVINCE_ALL";
	//<-
  	String sql = QUERY_DM_V_TPR_SUM_BY_PROVINCE_ALL;

    Map<String, TaskPerformanceRate> recordsExist = generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(sql,paramMap);

    List<AdminDivision> provinces = basicService.findAdminDivisionsByLevelDeep(AdminDivisionLevelDeep.PROVINCE);
    List<TaskPerformanceRate> rtnRates = Lists.newArrayList();
    for (AdminDivision adminDiv : provinces) {
      TaskPerformanceRate tpr = null;
      if ((tpr = recordsExist.get(adminDiv.getId())) != null) {
        rtnRates.add(tpr);
      } else {
        rtnRates.add(new TaskPerformanceRate(adminDiv.getName(), adminDiv.getId(), 0, 0));
      }
    }

    return rtnRates;
  }

  @Override
  public List<TaskPerformanceRate> taskPerformanceRateSummaryByProvince(String provinceAdCode) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for taskPerformanceRateSummaryByProvince(String provinceAdCode) report cache refresh...", "DM_MV_TPR_SUM_BY_PROVINCE");
//  	}
//    String sql = "select * from DM_V_TPR_SUM_BY_PROVINCE where " +
//        "P_VIEW_VCHAR2_PARAM.SET_PARAM('"+provinceAdCode+"')='"+provinceAdCode+"'";//jay:P_VIEW_VCHAR2_PARAM乃预定义的oracle package，以实现参数化视图
    //<-
  	String sql = QUERY_DM_V_TPR_SUM_BY_PROVINCE.replaceAll("\\u003F5", "'"+provinceAdCode+"'");
  	sql = sql.replaceAll("\\u003F6", "'"+provinceAdCode+"'");

    Map<String, TaskPerformanceRate> recordsExist = generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(sql,new HashMap<String, String>());

    AdminDivisionLegacy province = adminDivLegRepo.findOne(provinceAdCode);//fetch name only

    List<AdminDivision> cities = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(province.getName(), provinceAdCode, AdminDivisionLevelDeep.CITY);

    List<TaskPerformanceRate> rtnRates = Lists.newArrayList();
    for (AdminDivision city : cities) {
      expandTree1(city, rtnRates, recordsExist);
    }

    return rtnRates;
  }

  @Override
  public List<TaskPerformanceRate> taskPerformanceRateSummaryByCity(String cityAdCode) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for taskPerformanceRateSummaryByCity(String cityAdCode) report cache refresh...", "DM_MV_TPR_SUM_BY_CITY");
//  	}
//    String sql = "select * from DM_V_TPR_SUM_BY_CITY where " +
//        "P_VIEW_VCHAR2_PARAM.SET_PARAM('"+cityAdCode+"')='"+cityAdCode+"'";//jay:P_VIEW_VCHAR2_PARAM乃预定义的oracle package，以实现参数化视图
    //<-
  	String sql=
  			" SELECT PL.AD_NAME, PL.AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
  			" (SELECT CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME CITY_AD_NAME, CS.CHANNEL_AD_CODE AD_CODE, CS.CHANNEL_AD_NAME AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
  			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    	  " WHERE JR.CHANNEL = CS.ID AND "+//--关联任务和通路
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?1 "+
    	  " ?3 "+
  			" GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME " +
  			" HAVING CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"') PL " +//--计划任务数统计
  			" LEFT JOIN " +
  			" (SELECT CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, CS.CHANNEL_AD_CODE AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
  			" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    	  " WHERE JR.CHANNEL = CS.ID AND "+//--关联任务和通路
  			" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND "+ //--暂时把未经审核或审核成功的任务都视为成功任务
  			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' AND " + //--过滤出状态为完成的任务
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?2 "+
    	  " ?4 "+
  			" GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_AD_CODE " +
  			" HAVING CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"') AC " +//--实际任务完成数统计
  			" ON PL.CITY_AD_CODE = AC.CITY_AD_CODE AND PL.AD_CODE = AC.AD_CODE";

    Map<String, TaskPerformanceRate> recordsExist = generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(sql,new HashMap<String, String>());

    List<AdminDivision> cityAreas = Lists.newArrayList();
    boolean isCityAreaSpecial = false;
    //trying find special city area and expand
    AdminDivisionLegacy city = adminDivLegRepo.findOne(cityAdCode);//fetch name only
    List<AdminDivision> tryingSpecialCityAreas = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(city.getName(), cityAdCode, AdminDivisionLevelDeep.CITY);
    List<TaskPerformanceRate> tryingRates = Lists.newArrayList();
    for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
      expandTree1(tryingSpecialCityArea, tryingRates, recordsExist);
    }
    if (CollectionUtils.isEmpty(tryingRates)) {
      //非直辖市(如南京市(江苏省) etc.)处理:->
      tryingSpecialCityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);

      for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
        expandTree1(tryingSpecialCityArea, tryingRates, recordsExist);
      }

      for (TaskPerformanceRate tryingRate : tryingRates) {
        String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //->
    } else {
      //直辖市(如上海、北京、天津 etc.)处理:->
      for (TaskPerformanceRate tryingRate : tryingRates) {
        String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 保险起见，需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //->
    }

    //正常情况->
    if (!isCityAreaSpecial) {
      cityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
    }

    List<TaskPerformanceRate> rtnRates = Lists.newArrayList();
    for (AdminDivision area : cityAreas) {
      expandTree1(area, rtnRates, recordsExist);
    }
    //<-

    //在针对混合数据的场景(既有市辖区、又有直接区县，这两个不同维度数据合并在一起作为流入数据的情况)下，需要去除特殊数据
    if (isCityAreaSpecial) {
      List<TaskPerformanceRate> ratesNeedsAppending = Lists.newArrayList();
      List<AdminDivision> cityAreasWithSpecialDataMixed = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
      for (AdminDivision area : cityAreasWithSpecialDataMixed) {
        expandTree1(area, ratesNeedsAppending, recordsExist);
      }
      for (ListIterator<TaskPerformanceRate> iterator = ratesNeedsAppending.listIterator(); iterator.hasNext();) {
        TaskPerformanceRate sorNeedsAppending = iterator.next();
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(sorNeedsAppending.getAdminDivAreaName())) {
          iterator.remove();
        }
      }
      //和首轮ETL出的数据进行合并
      rtnRates.addAll(ratesNeedsAppending);
    }

    return rtnRates;
  }

  @Override
  public List<TaskPerformanceRate> storeVisitingTrackSummaryByCustomAreas() {
    List<TaskPerformanceRate> allTprs = storeVisitingTrackSummaryByProvinceALL(new HashMap<String, String>());
    Map<String, List<String>> areaReg = customAreaRegistry();
    Map<String, String[]> areaNameCodeReg = customAreaNameCodeRegistry();
    List<TaskPerformanceRate> rtnTprs = Lists.newArrayList();
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(EAST_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(SOUTH_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(WEST_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    rtnTprs.add(calculateTaskPerformanceRateByCustomArea(NORTH_AREA_CODE, allTprs, areaReg, areaNameCodeReg));
    return rtnTprs;
  }

  @Override
  public List<TaskPerformanceRate> storeVisitingTrackSummaryByCustomArea(String customAreaCode,Map<String, String> paramMap) {
    List<TaskPerformanceRate> allTprs = storeVisitingTrackSummaryByProvinceALL(paramMap);
    List<TaskPerformanceRate> rtnTprs = Lists.newArrayList();
    Map<String, List<String>> areaReg = customAreaRegistry();
    List<String> targetProvinceCodes = areaReg.get(customAreaCode);

    for (TaskPerformanceRate tpr : allTprs) {
      if (targetProvinceCodes.contains(tpr.getAdminDivAreaCode())) {
        rtnTprs.add(new TaskPerformanceRate(tpr.getAdminDivAreaName(), tpr.getAdminDivAreaCode(), tpr.getPlannedQuantityOfTask(), tpr.getActualQuantityOfTask()));
      }
    }
    return rtnTprs;
  }

  @Override
  public List<TaskPerformanceRate> storeVisitingTrackSummaryByProvinceALL(Map<String, String> paramMap) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for storeVisitingTrackSummaryByProvinceALL report cache refresh...", "DM_MV_TPR_SUM_BY_PROVINCE_ALL");
//  	}
    //String sql = "select * from DM_V_TPR_SUM_BY_PROVINCE_ALL";
  	//<-

  	String sql = QUERY_DM_V_TPR_SUM_BY_PROVINCE_ALL;

    Map<String, TaskPerformanceRate> recordsExist = generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(sql,paramMap);

    List<AdminDivision> provinces = basicService.findAdminDivisionsByLevelDeep(AdminDivisionLevelDeep.PROVINCE);
    List<TaskPerformanceRate> rtnRates = Lists.newArrayList();
    for (AdminDivision adminDiv : provinces) {
      TaskPerformanceRate tpr = null;
      if ((tpr = recordsExist.get(adminDiv.getId())) != null) {
        rtnRates.add(tpr);
      } else {
        rtnRates.add(new TaskPerformanceRate(adminDiv.getName(), adminDiv.getId(), 0, 0));
      }
    }

    return rtnRates;
  }

  @Override
  public List<TaskPerformanceRate> storeVisitingTrackSummaryByProvince(String provinceAdCode,Map<String,String> paramMap) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for storeVisitingTrackSummaryByProvince(String provinceAdCode) report cache refresh...", "DM_MV_TPR_SUM_BY_PROVINCE");
//  	}
//    String sql = "select * from DM_V_TPR_SUM_BY_PROVINCE where " +
//        "P_VIEW_VCHAR2_PARAM.SET_PARAM('"+provinceAdCode+"')='"+provinceAdCode+"'";//jay:P_VIEW_VCHAR2_PARAM乃预定义的oracle package，以实现参数化视图
  	//<-
  	String sql = QUERY_DM_V_TPR_SUM_BY_PROVINCE.replaceAll("\\u003F5", "'"+provinceAdCode+"'");
  	sql = sql.replaceAll("\\u003F6", "'"+provinceAdCode+"'");

    Map<String, TaskPerformanceRate> recordsExist = generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(sql,paramMap);

    AdminDivisionLegacy province = adminDivLegRepo.findOne(provinceAdCode);//fetch name only

    List<AdminDivision> cities = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(province.getName(), provinceAdCode, AdminDivisionLevelDeep.CITY);

    List<TaskPerformanceRate> rtnRates = Lists.newArrayList();
    for (AdminDivision city : cities) {
      expandTree1(city, rtnRates, recordsExist);
    }

    return rtnRates;
  }

  @Override
  public List<SupervisorTaskPerformanceRate> storeVisitingTrackSummaryByCity(String cityAdCode,Map<String,String> paramMap) {
    //jay:候选的物化视图和视图解决方案->
//  	if (rptCacheService.isNeedForceRefresh()) {
//  		rptCacheService.doRefresh("Profiling for storeVisitingTrackSummaryByCity(String cityAdCode) report cache refresh...", "DM_MV_STPR_BY_CITY");
//  	}
//  String sql = "select * from DM_V_STPR_BY_CITY where " +
//  "P_VIEW_VCHAR2_PARAM.SET_PARAM('"+cityAdCode+"')='"+cityAdCode+"' and " +
//  "P_VIEW_VCHAR2_2PARAMS.SET_PARAM1('"+startTime+"')='"+startTime+"' and " +
//  "P_VIEW_VCHAR2_2PARAMS.SET_PARAM2('"+endTime+"')='"+endTime+"'";
  	//<-

    //within today->
  	DateTime dtStart = new DateTime();
    dtStart = dtStart.withHourOfDay(0);
    dtStart = dtStart.withMinuteOfHour(0);
    dtStart = dtStart.withSecondOfMinute(0);
    dtStart = dtStart.withMillisOfSecond(0);
    String startTime = dtStart.toString(DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS"));


    DateTime dtEnd = new DateTime();
    dtEnd = dtEnd.withHourOfDay(23);
    dtEnd = dtEnd.withMinuteOfHour(59);
    dtEnd = dtEnd.withSecondOfMinute(59);
    dtEnd = dtEnd.withMillisOfSecond(999);
    String endTime = dtEnd.toString(DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS"));
    //<-
    String sql =
    		" SELECT PL.EXECUTOR_EMP_ID, PL.EXECUTOR_NAME, PL.EXECUTOR_TITLE, PL.AD_NAME, PL.AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY,PL.channel_address," +
    		" PL.channel_city_ad_name,  PL.job_name, PL.project_name FROM " +
    		" (SELECT JR.EMP_NAME EXECUTOR_NAME, JR.EMP_ID EXECUTOR_EMP_ID, JR.PROJ_EMP_TITLE EXECUTOR_TITLE, CS.CHANNEL_AD_NAME AD_NAME, CS.CHANNEL_AD_CODE AD_CODE,cs.channel_address, " +
    		" cs.channel_city_ad_name,jr.job_name,jr.project_name," +
    		" COUNT(*) PLANNEAD_QUANTITY " +
    		" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    		" WHERE CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"' AND "+
    		" JR.CHANNEL = CS.ID AND " + //关联任务和通路
    		" JR.PLAN_JOB_START_TIME BETWEEN TO_TIMESTAMP('"+startTime+"', 'yyyy-mm-dd hh24:mi:ss:ff') AND TO_TIMESTAMP('"+endTime+"', 'yyyy-mm-dd hh24:mi:ss:ff') AND " +
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?1 "+
    	  " ?3 "+
    		" GROUP BY JR.EMP_ID, JR.EMP_NAME, JR.PROJ_EMP_TITLE, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME,cs.channel_address,cs.channel_city_ad_name,jr.job_name,jr.project_name) PL " +//--计划巡店数统计
    		" LEFT JOIN " +
    		" (SELECT JR.EMP_ID, CS.CHANNEL_AD_CODE AD_CODE, COUNT(*) ACTUAL_QUANTITY FROM " +
    		" DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS " +
    		" WHERE CS.CHANNEL_CITY_AD_CODE = '"+cityAdCode+"' AND "+
    		" JR.CHANNEL = CS.ID AND " + //关联任务和通路
    		" (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) AND " +//--暂时把未经审核或审核成功的任务都视为成功任务
  			" JR.JOB_STATUS = '"+PlanJobResult.JOB_STATUS_FINISHED+"' " + //--过滤出状态为完成的任务
    		" AND JR.PLAN_JOB_START_TIME BETWEEN TO_TIMESTAMP('"+startTime+"', 'yyyy-mm-dd hh24:mi:ss:ff') AND TO_TIMESTAMP('"+endTime+"', 'yyyy-mm-dd hh24:mi:ss:ff') AND " +
  			" CS.TYPE = "+ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
    	  " ?2 "+
    	  " ?4 "+
    		" GROUP BY JR.EMP_ID, CS.CHANNEL_AD_CODE) AC " +//--实际任务完成数统计
    		" ON PL.EXECUTOR_EMP_ID = AC.EMP_ID AND PL.AD_CODE = AC.AD_CODE";

    List<SupervisorTaskPerformanceRate> supervisorsWithTheAreas = generateCommonStoreVisitingTrackRptResultViaSqlAndUserPermRole(sql);

    return supervisorsWithTheAreas;

    /**   不知道是干什么的?

    //凡是存在督导的城市辖区将会被记录下来->
    Map<String, String> supervisorExistsInTheAreaReg = Maps.newHashMap();
    for (SupervisorTaskPerformanceRate stpr : supervisorsWithTheAreas) {
      supervisorExistsInTheAreaReg.put(stpr.getAdminDivAreaCode(), stpr.getExecutorName());
    }
    //<-

    List<AdminDivision> cityAreas = Lists.newArrayList();
    boolean isCityAreaSpecial = false;
    //trying find special city area and expand
    AdminDivisionLegacy city = adminDivLegRepo.findOne(cityAdCode);//fetch name only
    List<AdminDivision> tryingSpecialCityAreas = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(city.getName(), cityAdCode, AdminDivisionLevelDeep.CITY);
    List<SupervisorTaskPerformanceRate> tryingEmptyRates = Lists.newArrayList();
    for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
      expandTree2(tryingSpecialCityArea, tryingEmptyRates, supervisorExistsInTheAreaReg);
    }
    if (CollectionUtils.isEmpty(tryingEmptyRates)) {
      //非直辖市(如南京市(江苏省) etc.)处理:->
      tryingSpecialCityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);

      for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
        expandTree2(tryingSpecialCityArea, tryingEmptyRates, supervisorExistsInTheAreaReg);
      }

      for (SupervisorTaskPerformanceRate tryingEmptyRate : tryingEmptyRates) {
        String tryingFirstRateAreaName = tryingEmptyRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingEmptyRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //<-
    } else {
      //直辖市(如上海、北京、天津 etc.)处理:->
      for (SupervisorTaskPerformanceRate tryingRate : tryingEmptyRates) {
        String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
        String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
        //发现特殊数据
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
          List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
          //trying another time...
          if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 保险起见，需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
            tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
          }
          cityAreas.addAll(tryingAdminDivs);
          isCityAreaSpecial = true;
        } else {
          break;
        }
      }
      //<-
    }

    //正常情况->
    if (!isCityAreaSpecial) {
      cityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
    }

    List<SupervisorTaskPerformanceRate> rtnEmptyRates = Lists.newArrayList();
    for (AdminDivision area : cityAreas) {
      expandTree2(area, rtnEmptyRates, supervisorExistsInTheAreaReg);
    }
    //<-

    //在针对混合数据的场景(既有市辖区、又有直接区县，这两个不同维度数据合并在一起作为流入数据的情况)下，需要去除特殊数据
    if (isCityAreaSpecial) {
      List<SupervisorTaskPerformanceRate> emptyRatesNeedsAppending = Lists.newArrayList();
      List<AdminDivision> cityAreasWithSpecialDataMixed = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
      for (AdminDivision area : cityAreasWithSpecialDataMixed) {
        expandTree2(area, emptyRatesNeedsAppending, supervisorExistsInTheAreaReg);
      }
      for (ListIterator<SupervisorTaskPerformanceRate> iterator = emptyRatesNeedsAppending.listIterator(); iterator.hasNext();) {
        SupervisorTaskPerformanceRate stprNeedsAppending = iterator.next();
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(stprNeedsAppending.getAdminDivAreaName())) {
          iterator.remove();
        }
      }
      //和首轮ETL出的数据进行合并
      rtnEmptyRates.addAll(emptyRatesNeedsAppending);
    }

    supervisorsWithTheAreas.addAll(rtnEmptyRates);
    return supervisorsWithTheAreas;


    **/


  }

  private StoreOpeningRate calculateStoreOpeningRateByCustomArea(String customAreaCode, List<StoreOpeningRate> allSors, Map<String, List<String>> areaReg,
      Map<String, String[]> areaNameCodeReg) {
    List<String> provinceAdCodesOfTheArea = areaReg.get(customAreaCode);
    int totalPlannedQuantity = 0;
    int totalActualQuantity = 0;
    for (StoreOpeningRate sor : allSors) {
      if (provinceAdCodesOfTheArea.contains(sor.getAdminDivAreaCode())) {
        totalPlannedQuantity += sor.getPlannedQuantityOfStoreOpening();
        totalActualQuantity += sor.getActualQuantityOfStoreOpening();
      }
    }
    return
        new StoreOpeningRate(areaNameCodeReg.get(customAreaCode)[0], areaNameCodeReg.get(customAreaCode)[1], totalPlannedQuantity, totalActualQuantity);
  }

  private TaskPerformanceRate calculateTaskPerformanceRateByCustomArea(String customAreaCode, List<TaskPerformanceRate> allTprs, Map<String, List<String>> areaReg,
      Map<String, String[]> areaNameCodeReg) {
    List<String> provinceAdCodesOfTheArea = areaReg.get(customAreaCode);
    int totalPlannedQuantity = 0;
    int totalActualQuantity = 0;
    for (TaskPerformanceRate tpr : allTprs) {
      if (provinceAdCodesOfTheArea.contains(tpr.getAdminDivAreaCode())) {
        totalPlannedQuantity += tpr.getPlannedQuantityOfTask();
        totalActualQuantity += tpr.getActualQuantityOfTask();
      }
    }
    return
        new TaskPerformanceRate(areaNameCodeReg.get(customAreaCode)[0], areaNameCodeReg.get(customAreaCode)[1], totalPlannedQuantity, totalActualQuantity);
  }

  private Map<String, StoreOpeningRate> storeOpeningRateReportHalfBuiltBy(String sql) {
    Map<String,StoreOpeningRate> recordsExist = jdbcTemplate.execute(sql, new PreparedStatementCallback<Map<String, StoreOpeningRate>>() {

      @Override
      public Map<String, StoreOpeningRate> doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
        Map<String,StoreOpeningRate> rtnMap = new LinkedHashMap<String,StoreOpeningRate>();
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
          String areaName = rs.getString(1);
          String areaCode = rs.getString(2);
          int plQuantity = rs.getInt(3);
          int acQuantity = rs.getInt(4);
          rtnMap.put(areaCode, new StoreOpeningRate(areaName, areaCode, plQuantity, acQuantity));
        }
        return rtnMap;
      }
    });
    return recordsExist;
  }

  private Map<String, TaskPerformanceRate> taskPerformanceRateReportHalfBuiltBy(String sql) {
    Map<String,TaskPerformanceRate> recordsExist = jdbcTemplate.execute(sql, new PreparedStatementCallback<Map<String, TaskPerformanceRate>>() {

      @Override
      public Map<String, TaskPerformanceRate> doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
        Map<String,TaskPerformanceRate> rtnMap = new LinkedHashMap<String,TaskPerformanceRate>();
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
          String areaName = rs.getString(1);
          String areaCode = rs.getString(2);
          int plQuantity = rs.getInt(3);
          int acQuantity = rs.getInt(4);
          rtnMap.put(areaCode, new TaskPerformanceRate(areaName, areaCode, plQuantity, acQuantity));
        }
        return rtnMap;
      }
    });
    return recordsExist;
  }

  private List<SupervisorTaskPerformanceRate> storeVisitingTrackReportHalfBuiltBy(String sql) {
    List<SupervisorTaskPerformanceRate> supervisorsWithTheAreas = jdbcTemplate.execute(sql, new PreparedStatementCallback<List<SupervisorTaskPerformanceRate>>() {

      @Override
      public List<SupervisorTaskPerformanceRate> doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
        List<SupervisorTaskPerformanceRate> result = Lists.newArrayList();
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
          String executorEmpId = rs.getString(1);
          String executorName = rs.getString(2);
          String executorTitle = rs.getString(3);
          String areaName = rs.getString(4);
          String areaCode = rs.getString(5);
          int plQuantity = rs.getInt(6);
          int acQuantity = rs.getInt(7);

          SupervisorTaskPerformanceRate stpr = new SupervisorTaskPerformanceRate(executorEmpId, executorName, executorTitle, areaName, areaCode, plQuantity, acQuantity);
          stpr.setExecuteAddress(rs.getString("channel_address"));

          stpr.setExecuteJobName(rs.getString("job_name"));
          stpr.setExecuteProjectName(rs.getString("project_name"));
          stpr.setExecuteCityName(rs.getString("channel_city_ad_name"));

          result.add(stpr);
        }
        return result;
      }
    });
    return supervisorsWithTheAreas;
  }

//	private Map<String, StoreOpeningRate> generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(String preSql,StoreOpeningRateReportCriteria criteria) {
//		return generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(preSql, null);
//	}


	private Map<String, StoreOpeningRate> generateCommonStoreOpeningRateRptResultViaSqlAndUserPermRole(String preSql, StoreOpeningRateReportCriteria criteria) {
		User user = userService.getCurrentUserWithPermissionRoleAndEntries();
		PermissionRole permRole = user.getPermissionRole();


//		String projectCodesUserParticipatedIn = calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOf(user);
//		String projectCodesUserParticipatedIn="";
//		List<String> projectsUserParticipatedIn = projectEmployeeRepository.findOwnProjectIds(user.getEmployee().getEmpId());
//		if (!CollectionUtils.isEmpty(projectsUserParticipatedIn)){
//			StringBuffer buffer = new StringBuffer();
//			for (String projectCode : projectsUserParticipatedIn) {
//		      	buffer.append("," + "'" +projectCode + "'");
//		  	}
//			projectCodesUserParticipatedIn= buffer.toString().replaceFirst(",", "");
//		}

		String projectCodesUserParticipatedIn="select t2.code from DM_PROJECT_EMPLOYEE t1,DM_PROJECT t2 where t1.emp_id= "+user.getEmployee().getEmpId()+" and t1.project=t2.id";

		// 一般用户->
		if (!permRole.isAdvanced()) {
			if(projectCodesUserParticipatedIn.trim().length()<1){
				return Maps.newHashMap();
			}

			preSql = preSql.replaceAll("\\u003F1", "AND PJ.CODE IN("+ projectCodesUserParticipatedIn + ") ");
			preSql = preSql.replaceAll("\\u003F2", "AND JR.PROJECT_CODE IN("+ projectCodesUserParticipatedIn + ") ");

			if(permRole.getUseADFilter()==1){
				String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
//						calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOf(user);
				preSql = preSql.replaceAll("\\u003F3"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
				preSql = preSql.replaceAll("\\u003F4"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
			}else{
				preSql = preSql.replaceAll("\\u003F3", "");
				preSql = preSql.replaceAll("\\u003F4", "");
			}

			if (criteria == null) {
				preSql = preSql.replaceAll("\\u003F5", "");
				preSql = preSql.replaceAll("\\u003F6", "");
				preSql = preSql.replaceAll("\\u003F7", "");
				preSql = preSql.replaceAll("\\u003F8", "");
				preSql = preSql.replaceAll("\\u003F9", "");
				preSql = preSql.replaceAll("\\u003F0", "");
			} else {

				preSql = criteria.toQueryConditionSQL(preSql);
			}


		} else {

			preSql = preSql.replaceAll("\\u003F1", "");
			preSql = preSql.replaceAll("\\u003F2", "");


//			String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOf(user);
			String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);


			preSql = preSql.replaceAll("\\u003F3"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
			preSql = preSql.replaceAll("\\u003F4"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");

			if (criteria == null) {
				preSql = preSql.replaceAll("\\u003F5", "");
				preSql = preSql.replaceAll("\\u003F6", "");
				preSql = preSql.replaceAll("\\u003F7", "");
				preSql = preSql.replaceAll("\\u003F8", "");
				preSql = preSql.replaceAll("\\u003F9", "");
				preSql = preSql.replaceAll("\\u003F0", "");
			} else {

				preSql = criteria.toQueryConditionSQL(preSql);
			}

		}

		return storeOpeningRateReportHalfBuiltBy(preSql);
	}

	private Map<String, TaskPerformanceRate> generateCommonTaskPerformanceRateRptResultViaSqlAndUserPermRole(String preSql, Map<String,String> paramMap) {

		// TODO 慢慢慢

		User user = userService.getCurrentUserWithPermissionRoleAndEntries();
		PermissionRole permRole = user.getPermissionRole();

		String projectCodesUserParticipatedIn ="";
		if(StringUtils.isNotBlank(paramMap.get("projectCode"))){
			projectCodesUserParticipatedIn ="'"+paramMap.get("projectCode")+"'";
		}else{
			projectCodesUserParticipatedIn = calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOfSQL(user);
		}



		Map<String, TaskPerformanceRate> recordsExist = Maps.newHashMap();
		if (StringUtils.isNotBlank(projectCodesUserParticipatedIn)) {
			preSql = preSql.replaceAll("\\u003F1", "AND JR.PROJECT_CODE IN("+ projectCodesUserParticipatedIn + ") ");
			preSql = preSql.replaceAll("\\u003F2", "AND JR.PROJECT_CODE IN("+ projectCodesUserParticipatedIn + ") ");
			// 一般用户->
			if (!permRole.isAdvanced()) {



				if(permRole.getUseADFilter()==1){
					String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
//							calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOf(user);
					preSql = preSql.replaceAll("\\u003F3"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
					preSql = preSql.replaceAll("\\u003F4"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
				}else{
					preSql = preSql.replaceAll("\\u003F3", "");
					preSql = preSql.replaceAll("\\u003F4", "");
				}

//				preSql = preSql.replaceAll("\\u003F3", "");
//				preSql = preSql.replaceAll("\\u003F4", "");


				recordsExist = taskPerformanceRateReportHalfBuiltBy(preSql);
				// <-
				// 高级管理者用户->
			} else {
				String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
				if (StringUtils.isNotBlank(topLevelAdminDivCodesUserManaged)) {
					preSql = preSql.replaceAll("\\u003F3"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
					preSql = preSql.replaceAll("\\u003F4"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
					recordsExist = taskPerformanceRateReportHalfBuiltBy(preSql);
				}
			}
			// <-
		}
		return recordsExist;
	}

	private String calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOfSQL(User user) {
		PermissionRole permRole = user.getPermissionRole();
		String sql="";
		if (!permRole.isAdvanced()) {
			sql=" select t2.code from DM_PROJECT_EMPLOYEE t1,DM_PROJECT t2 where t1.emp_id= "+user.getEmployee().getEmpId()+" and t1.project=t2.id ";
		}else{
			sql=" select t.code from DM_PROJECT t ";
		}
		return sql;
	}



	private List<SupervisorTaskPerformanceRate> generateCommonStoreVisitingTrackRptResultViaSqlAndUserPermRole(String preSql) {
		User user = userService.getCurrentUserWithPermissionRoleAndEntries();
    PermissionRole permRole = user.getPermissionRole();

    String projectCodesUserParticipatedIn = calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOfSQL(user);
    List<SupervisorTaskPerformanceRate> supervisorsWithTheAreas = Lists.newArrayList();
    if (StringUtils.isNotBlank(projectCodesUserParticipatedIn)) {
    	preSql = preSql.replaceAll("\\u003F1", "AND JR.PROJECT_CODE IN("+projectCodesUserParticipatedIn+") ");
    	preSql = preSql.replaceAll("\\u003F2", "AND JR.PROJECT_CODE IN("+projectCodesUserParticipatedIn+") ");
    	//一般用户->
      if (!permRole.isAdvanced()) {


    	  if(permRole.getUseADFilter()==1){
				String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
//						calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOf(user);
				preSql = preSql.replaceAll("\\u003F3"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
				preSql = preSql.replaceAll("\\u003F4"," AND CS.CHANNEL_PROVINCE_AD_CODE IN("+ topLevelAdminDivCodesUserManaged + ") ");
			}else{
				preSql = preSql.replaceAll("\\u003F3", "");
				preSql = preSql.replaceAll("\\u003F4", "");
			}


//      	preSql = preSql.replaceAll("\\u003F3", "");
//      	preSql = preSql.replaceAll("\\u003F4", "");

        supervisorsWithTheAreas = storeVisitingTrackReportHalfBuiltBy(preSql);
      //<-
      //高级管理者用户->
      } else {
      	String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
        if (StringUtils.isNotBlank(topLevelAdminDivCodesUserManaged)) {
        	preSql = preSql.replaceAll("\\u003F3", " AND CS.CHANNEL_PROVINCE_AD_CODE IN("+topLevelAdminDivCodesUserManaged+") ");
        	preSql = preSql.replaceAll("\\u003F4", " AND CS.CHANNEL_PROVINCE_AD_CODE IN("+topLevelAdminDivCodesUserManaged+") ");

          supervisorsWithTheAreas = storeVisitingTrackReportHalfBuiltBy(preSql);
        }
      }
      //<-
    }
		return supervisorsWithTheAreas;
	}

  private static <TK extends Serializable>void expandTree(AbstractTreeNode<TK> entryNode, List<StoreOpeningRate> rtnRates, Map<String,StoreOpeningRate> recordsExist) {

    if (CollectionUtils.isEmpty(entryNode.getChilds())) {
      logger.debug("leaf node:" + entryNode +",with parent:"+(entryNode.getParent()!=null?entryNode.getParent():"null"));
      return;//touch leaf node
    } else {
      for (AbstractTreeNode<TK> childNode : entryNode.getChilds()) {
        logger.debug("normal node:" + childNode +",with parent:"+(childNode.getParent()!=null?childNode.getParent():"null"));

        StoreOpeningRate sor = null;
        if ((sor = recordsExist.get(childNode.getId())) != null) {
          rtnRates.add(sor);
        } else {
          rtnRates.add(new StoreOpeningRate(childNode.getName(), (String)childNode.getId(), 0, 0));
        }

        expandTree(childNode, rtnRates, recordsExist);
      }
    }
  }

  //返回不重复的项目Code集合
  private List<String> getDistinctProjectCodes(List<Project> projects) {
    Set<String> targetProjectCodeSet = Sets.newHashSet();
    for (Project project : projects) {
    	if (project.getCode() != null)
    		targetProjectCodeSet.add(project.getCode());
    }
    if (targetProjectCodeSet.size() == 0) return Lists.newEmptyArrayList();

    return Lists.newArrayList(targetProjectCodeSet);
  }

	private String calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOf(User user) {
		PermissionRole permRole = user.getPermissionRole();
		String projectCodesUserParticipatedIn = null;
    //一般用户->
    if (!permRole.isAdvanced()) {
      List<Project> projectsUserParticipatedIn = projectService.findByUserOwn(user);
      if (CollectionUtils.isEmpty(projectsUserParticipatedIn)) return null;
      StringBuffer buffer = new StringBuffer();
      for (Project project : projectsUserParticipatedIn) {
      	buffer.append("," + "'" +project.getCode() + "'");
  		}
      projectCodesUserParticipatedIn = buffer.toString().replaceFirst(",", "");
    //<-

    //高级管理者用户->
    } else {
      if (CollectionUtils.isEmpty(permRole.getManagedOrgCodeEntries())) {
        return null;
      }
      List<Long> managerEmpIds = projRepo.findByOrgCodeIn(permRole.getManagedOrgCodeEntries());
      if (CollectionUtils.isEmpty(managerEmpIds)) {
        return null;
      }
      List<Project> projectsUserParticipatedIn = Lists.newArrayList();
      for (Long managerEmpId : managerEmpIds) {
        List<Project> targetProjects = projectService.findByEmployeeOwn(new OrgnizationEmployee(managerEmpId));
        projectsUserParticipatedIn.addAll(targetProjects);
			}
      if (CollectionUtils.isEmpty(projectsUserParticipatedIn)) return null;
      List<String> projectCodes = getDistinctProjectCodes(projectsUserParticipatedIn);
      StringBuffer buffer = new StringBuffer();
      for (String projectCode : projectCodes) {
      	buffer.append("," + "'" + projectCode + "'");
  		}
      projectCodesUserParticipatedIn = buffer.toString().replaceFirst(",", "");
    }

		return projectCodesUserParticipatedIn;
	}


	@SuppressWarnings("unused")
	private String calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOf(User advancedUser) {

		//勿用 danger！！
		//Validate.isTrue(advancedUser.getPermissionRole().isAdvanced(), "本方法仅适用于高级管理者用户");


		PermissionRole permRole = advancedUser.getPermissionRole();
	  	String topLevelAdminDivCodesAdUserManaged = null;
	  	List<String> adminDivCodesAdUserManagerd = permRole.getManagedAdminDivisionCodeEntries();
	    if (CollectionUtils.isEmpty(adminDivCodesAdUserManagerd)) {
	    	return null;
	    }
	    StringBuffer buffer = new StringBuffer();
	  	for (String adminDivCode : adminDivCodesAdUserManagerd) {
	    	buffer.append("," + "'" + adminDivCode + "'");
		}
	  	topLevelAdminDivCodesAdUserManaged = buffer.toString().replaceFirst(",", "");
	  	return topLevelAdminDivCodesAdUserManaged;
	}


	private String calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(User advancedUser) {
		String sql="select dpadv.managed_admindiv_adcode from DM_PERMROLE_ADMIN_DIV_ADCODE dpadv where dpadv.permission_role = "+advancedUser.getPermissionRole().getId();
	  	return sql;
	}

  private static <TK extends Serializable>void expandTree1(AbstractTreeNode<TK> entryNode, List<TaskPerformanceRate> rtnRates, Map<String,TaskPerformanceRate> recordsExist) {

    if (CollectionUtils.isEmpty(entryNode.getChilds())) {
      logger.debug("leaf node:" + entryNode +",with parent:"+(entryNode.getParent()!=null?entryNode.getParent():"null"));
      return;//touch leaf node
    } else {
      for (AbstractTreeNode<TK> childNode : entryNode.getChilds()) {
        logger.debug("normal node:" + childNode +",with parent:"+(childNode.getParent()!=null?childNode.getParent():"null"));

        TaskPerformanceRate tpr = null;
        if ((tpr = recordsExist.get(childNode.getId())) != null) {
          rtnRates.add(tpr);
        } else {
          rtnRates.add(new TaskPerformanceRate(childNode.getName(), (String)childNode.getId(), 0, 0));
        }

        expandTree1(childNode, rtnRates, recordsExist);
      }
    }
  }

  private static <TK extends Serializable>void expandTree2(AbstractTreeNode<TK> entryNode, List<SupervisorTaskPerformanceRate> rtnRates, Map<String, String> supervisorExistsInTheAreaReg) {

    if (CollectionUtils.isEmpty(entryNode.getChilds())) {
      logger.debug("leaf node:" + entryNode +",with parent:"+(entryNode.getParent()!=null?entryNode.getParent():"null"));
      return;//touch leaf node
    } else {
      for (AbstractTreeNode<TK> childNode : entryNode.getChilds()) {
        logger.debug("normal node:" + childNode +",with parent:"+(childNode.getParent()!=null?childNode.getParent():"null"));

        if (supervisorExistsInTheAreaReg.get(childNode.getId()) == null) {
          //不存在督导的地区填入一些空信息(表示无督导管辖之区域)并把计划和任务数补为0
          rtnRates.add(new SupervisorTaskPerformanceRate(childNode.getName(), (String)childNode.getId(), 0, 0));
        }

        expandTree2(childNode, rtnRates, supervisorExistsInTheAreaReg);
            }
        }
    }

    @Override
    public SellIntoRate sellIntoRateSummaryByCountry() {
        List<SellIntoRate> sellIntoRates = sellIntoRateSummaryByProvinceALL();
        int totalPlannedQuantity = 0;
        int totalActualQuantity = 0;
        for (SellIntoRate sellIntoRate : sellIntoRates) {
            totalPlannedQuantity += sellIntoRate.getPlannedQuantityOfSellInto();
            totalActualQuantity += sellIntoRate.getActualQuantityOfSellInto();
        }
        return new SellIntoRate("全国", "", totalPlannedQuantity, totalActualQuantity);
    }

    @Override
    public List<SellIntoRate> sellIntoRateSummaryByProvince(String provinceAdCode) {
        String sql =
                " SELECT PL.CHANNEL_CITY_AD_NAME, PL.CHANNEL_CITY_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
                        " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
                        " FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                        " WHERE PS.PROJECT = PJ.ID AND " + //--关联门店和项目
                        " PJ.STATUS = 1 AND " +//--指定项目状态(Active，未完成即进行中状态)
                        " PS.CHANNEL = CS.ID AND " +//--关联门店和通路
                        " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                        " ?1 " +
                        " ?3 " +
                        " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME, CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME " +
                        " HAVING CS.CHANNEL_PROVINCE_AD_CODE = '" + provinceAdCode + "') PL " +
                        " LEFT JOIN " +
                        " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
                        " FROM DM_SELLINTO SI, DM_PROJECT_STORE S, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                        " WHERE SI.STATUS = " + SellIntoStatus.SUCCESS.ordinal() + " AND " +
                        " SI.PROJECT_STORE = S.ID AND S.CHANNEL = CS.ID AND PJ.ID=S.PROJECT AND " +//--关联任务
                        " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                        " ?2 " +
                        " ?4 " +
                        " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_CITY_AD_CODE " +
                        " HAVING CS.CHANNEL_PROVINCE_AD_CODE = '" + provinceAdCode + "') AC " +
                        " ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE AND PL.CHANNEL_CITY_AD_CODE = AC.CITY_AD_CODE";

        Map<String, SellIntoRate> recordsExist = generateCommonSellIntoRateRptResultViaSqlAndUserPermRole(sql);

        AdminDivisionLegacy province = adminDivLegRepo.findOne(provinceAdCode);//fetch name only

        List<AdminDivision> cities = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(province.getName(), provinceAdCode, AdminDivisionLevelDeep.CITY);

        List<SellIntoRate> rtnRates = Lists.newArrayList();
        for (AdminDivision city : cities) {
            expandTree4SellInto(city, rtnRates, recordsExist);
        }

        return rtnRates;
    }

    @Override
    public List<SellIntoRate> sellIntoRateSummaryByCity(String cityAdCode) {
        String sql = " SELECT PL.CHANNEL_AD_NAME, PL.CHANNEL_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
                " (SELECT CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
                " FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                " WHERE PS.PROJECT = PJ.ID AND " + //--关联门店和项目
                " PJ.STATUS = 1 AND " +//--指定项目状态(Active，未完成即进行中状态)
                " PS.CHANNEL = CS.ID AND " +//--关联门店和通路
                " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                " ?1 " +
                " ?3 " +
                " GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_CITY_AD_NAME, CS.CHANNEL_AD_CODE, CS.CHANNEL_AD_NAME " +
                " HAVING CS.CHANNEL_CITY_AD_CODE = '" + cityAdCode + "') PL " + //--计划开店统计
                " LEFT JOIN " +
                " (SELECT CS.CHANNEL_CITY_AD_CODE CITY_AD_CODE, CS.CHANNEL_AD_CODE AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
                " FROM DM_SELLINTO SI, DM_PROJECT_STORE S, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                " WHERE SI.STATUS = " + SellIntoStatus.SUCCESS.ordinal() + " AND " +
                " SI.PROJECT_STORE = S.ID AND S.CHANNEL = CS.ID AND PJ.ID=S.PROJECT AND " +//--关联任务
                " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                " ?2 " +
                " ?4 " +
                " GROUP BY CS.CHANNEL_CITY_AD_CODE, CS.CHANNEL_AD_CODE " +
                " HAVING CS.CHANNEL_CITY_AD_CODE = '" + cityAdCode + "') AC " +
                " ON PL.CHANNEL_CITY_AD_CODE = AC.CITY_AD_CODE AND PL.CHANNEL_AD_CODE = AC.AD_CODE";

        Map<String, SellIntoRate> recordsExist = generateCommonSellIntoRateRptResultViaSqlAndUserPermRole(sql);

        List<AdminDivision> cityAreas = Lists.newArrayList();
        boolean isCityAreaSpecial = false;
        //trying find special city area and expand
        AdminDivisionLegacy city = adminDivLegRepo.findOne(cityAdCode);//fetch name only
        List<AdminDivision> tryingSpecialCityAreas = basicService.findAdminDivisionsByParentProvinceAdNameAndLevelDeep(city.getName(), cityAdCode, AdminDivisionLevelDeep.CITY);
        List<SellIntoRate> tryingRates = Lists.newArrayList();
        for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
            expandTree4SellInto(tryingSpecialCityArea, tryingRates, recordsExist);
        }
        if (CollectionUtils.isEmpty(tryingRates)) {
            //非直辖市(如南京市(江苏省) etc.)处理:->
            tryingSpecialCityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);

            for (AdminDivision tryingSpecialCityArea : tryingSpecialCityAreas) {
                expandTree4SellInto(tryingSpecialCityArea, tryingRates, recordsExist);
            }

            for (SellIntoRate tryingRate : tryingRates) {
                String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
                String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
                //发现特殊数据
                if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
                    List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
                    //trying another time...
                    if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
                        tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
                    }
                    cityAreas.addAll(tryingAdminDivs);
                    isCityAreaSpecial = true;
                } else {
                    break;
                }
            }
            //->
        } else {
            //直辖市(如上海、北京、天津 etc.)处理:->
            for (SellIntoRate tryingRate : tryingRates) {
                String tryingFirstRateAreaName = tryingRate.getAdminDivAreaName();
                String tryingFirstRateAreaCode = tryingRate.getAdminDivAreaCode();
                //发现特殊数据
                if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(tryingFirstRateAreaName)) {
                    List<AdminDivision> tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(tryingFirstRateAreaName, tryingFirstRateAreaCode);
                    //trying another time...
                    if (CollectionUtils.isEmpty(tryingAdminDivs)) {//jay: e.g. 保险起见，需要根据'xx市市辖区'这个名字再搜索一次，因为在基础数据中，存在如此编码的可能性
                        tryingAdminDivs = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName() + tryingFirstRateAreaName, tryingFirstRateAreaCode);
                    }
                    cityAreas.addAll(tryingAdminDivs);
                    isCityAreaSpecial = true;
                } else {
                    break;
                }
            }
            //->
        }

        //正常情况->
        if (!isCityAreaSpecial) {
            cityAreas = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
        }

        List<SellIntoRate> rtnRates = Lists.newArrayList();
        for (AdminDivision area : cityAreas) {
            expandTree4SellInto(area, rtnRates, recordsExist);
        }
        //<-

        //在针对混合数据的场景(既有市辖区、又有直接区县，这两个不同维度数据合并在一起作为流入数据的情况)下，需要去除特殊数据
        if (isCityAreaSpecial) {
            List<SellIntoRate> ratesNeedsAppending = Lists.newArrayList();
            List<AdminDivision> cityAreasWithSpecialDataMixed = basicService.findAdminDivisionsByParentCityAdNameAndAdCode(city.getName(), cityAdCode);
            for (AdminDivision area : cityAreasWithSpecialDataMixed) {
                expandTree4SellInto(area, ratesNeedsAppending, recordsExist);
            }
            for (ListIterator<SellIntoRate> iterator = ratesNeedsAppending.listIterator(); iterator.hasNext(); ) {
                SellIntoRate sorNeedsAppending = iterator.next();
                if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(sorNeedsAppending.getAdminDivAreaName())) {
                    iterator.remove();
                }
            }
            //和首轮处理过的数据进行合并
            rtnRates.addAll(ratesNeedsAppending);
        }

        return rtnRates;
    }

    @Override
    public List<SellIntoRate> sellIntoRateSummaryByProvinceALL() {
        String sql = " SELECT PL.PROVINCE_AD_NAME, PL.PROVINCE_AD_CODE, NVL(PL.PLANNEAD_QUANTITY, 0) PLANNEAD_QUANTITY, NVL(AC.ACTUAL_QUANTITY, 0) ACTUAL_QUANTITY FROM " +
                " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME PROVINCE_AD_NAME, COUNT(*) PLANNEAD_QUANTITY " +
                " FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                " WHERE PS.PROJECT = PJ.ID AND " +// --关联门店和项目
                " PJ.STATUS = 1 AND " +//--指定项目状态(Active，未完成即进行中状态)
                " PS.CHANNEL = CS.ID AND " +//--关联门店和通路
                " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                " ?1 " +
                " ?3 " +
                " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE, CS.CHANNEL_PROVINCE_AD_NAME) PL" +//--计划卖进统计
                " LEFT JOIN " +
                " (SELECT CS.CHANNEL_PROVINCE_AD_CODE PROVINCE_AD_CODE, COUNT(*) ACTUAL_QUANTITY " +
                " FROM DM_SELLINTO SI, DM_PROJECT_STORE S, DM_PROJECT PJ, DM_CHANNEL_SYNC CS " +
                " WHERE SI.STATUS = " + SellIntoStatus.SUCCESS.ordinal() + " AND " +//过滤出成功的卖进任务
                " SI.PROJECT_STORE = S.ID AND S.CHANNEL = CS.ID AND PJ.ID=S.PROJECT AND " +//--关联任务
                " CS.TYPE = " + ChannelSync.TYPE_CONCERNED_ABOUT_GPS +//--过滤出需要通路GPS的数据
                " ?2 " +
                " ?4 " +
                " GROUP BY CS.CHANNEL_PROVINCE_AD_CODE) AC " +//--实际开店统计
                " ON PL.PROVINCE_AD_CODE = AC.PROVINCE_AD_CODE " +//--关联计划和实际
                " ORDER BY PL.PROVINCE_AD_CODE";

        Map<String, SellIntoRate> recordsExist = generateCommonSellIntoRateRptResultViaSqlAndUserPermRole(sql);

        List<AdminDivision> provinces = basicService.findAdminDivisionsByLevelDeep(AdminDivisionLevelDeep.PROVINCE);
        List<SellIntoRate> rtnRates = Lists.newArrayList();
        for (AdminDivision adminDiv : provinces) {
            SellIntoRate sellIntoRate = null;
            if ((sellIntoRate = recordsExist.get(adminDiv.getId())) != null) {
                rtnRates.add(sellIntoRate);
            } else {
                rtnRates.add(new SellIntoRate(adminDiv.getName(), adminDiv.getId(), 0, 0));
            }
        }
        return rtnRates;
    }

    @Override
    public List<SellIntoRate> sellIntoRateSummaryByCustomAreas() {
        List<SellIntoRate> allSors = sellIntoRateSummaryByProvinceALL();
        Map<String, List<String>> areaReg = customAreaRegistry();
        Map<String, String[]> areaNameCodeReg = customAreaNameCodeRegistry();
        List<SellIntoRate> rtnSors = Lists.newArrayList();
        rtnSors.add(calculateSellIntoRateByCustomArea(EAST_AREA_CODE, allSors, areaReg, areaNameCodeReg));
        rtnSors.add(calculateSellIntoRateByCustomArea(SOUTH_AREA_CODE, allSors, areaReg, areaNameCodeReg));
        rtnSors.add(calculateSellIntoRateByCustomArea(WEST_AREA_CODE, allSors, areaReg, areaNameCodeReg));
        rtnSors.add(calculateSellIntoRateByCustomArea(NORTH_AREA_CODE, allSors, areaReg, areaNameCodeReg));
        return rtnSors;
    }


    private SellIntoRate calculateSellIntoRateByCustomArea(String customAreaCode, List<SellIntoRate> allSors, Map<String, List<String>> areaReg,
                                                           Map<String, String[]> areaNameCodeReg) {
        List<String> provinceAdCodesOfTheArea = areaReg.get(customAreaCode);
        int totalPlannedQuantity = 0;
        int totalActualQuantity = 0;
        for (SellIntoRate sor : allSors) {
            if (provinceAdCodesOfTheArea.contains(sor.getAdminDivAreaCode())) {
                totalPlannedQuantity += sor.getPlannedQuantityOfSellInto();
                totalActualQuantity += sor.getActualQuantityOfSellInto();
            }
        }
        return new SellIntoRate(areaNameCodeReg.get(customAreaCode)[0], areaNameCodeReg.get(customAreaCode)[1], totalPlannedQuantity, totalActualQuantity);

    }

    @Override
    public List<SellIntoRate> sellIntoRateSummaryByCustomArea(String customAreaCode) {
        List<SellIntoRate> allSors = sellIntoRateSummaryByProvinceALL();
        List<SellIntoRate> rtnSors = Lists.newArrayList();
        Map<String, List<String>> areaReg = customAreaRegistry();
        List<String> targetProvinceCodes = areaReg.get(customAreaCode);

        for (SellIntoRate sor : allSors) {
            if (targetProvinceCodes.contains(sor.getAdminDivAreaCode())) {
                rtnSors.add(new SellIntoRate(sor.getAdminDivAreaName(), sor.getAdminDivAreaCode(), sor.getPlannedQuantityOfSellInto(), sor.getActualQuantityOfSellInto()));
            }
        }
        return rtnSors;
    }

    private Map<String, SellIntoRate> generateCommonSellIntoRateRptResultViaSqlAndUserPermRole(String preSql) {
        User user = userService.getCurrentUserWithPermissionRoleAndEntries();
        PermissionRole permRole = user.getPermissionRole();

        String projectCodesUserParticipatedIn = calculateAndGenerateProjectCodesUserParticipatedInQueryConditionOf(user);
        Map<String, SellIntoRate> recordsExist = Maps.newHashMap();
        if (StringUtils.isNotBlank(projectCodesUserParticipatedIn)) {
            preSql = preSql.replaceAll("\\u003F1", "AND PJ.CODE IN(" + projectCodesUserParticipatedIn + ") ");
            preSql = preSql.replaceAll("\\u003F2", "AND PJ.CODE IN(" + projectCodesUserParticipatedIn + ") ");
            if (!permRole.isAdvanced()) {
                //一般用户->
                preSql = preSql.replaceAll("\\u003F3", "");
                preSql = preSql.replaceAll("\\u003F4", "");
                recordsExist = sellIntoRateReportHalfBuiltBy(preSql);
                //<-
            } else {
                //高级管理者用户->
                String topLevelAdminDivCodesUserManaged = calculateAndGenerateAdminDivCodesAdvancedUserManagedQueryConditionOfSQL(user);
                if (StringUtils.isNotBlank(topLevelAdminDivCodesUserManaged)) {
                    preSql = preSql.replaceAll("\\u003F3", " AND CS.CHANNEL_PROVINCE_AD_CODE IN(" + topLevelAdminDivCodesUserManaged + ") ");
                    preSql = preSql.replaceAll("\\u003F4", " AND CS.CHANNEL_PROVINCE_AD_CODE IN(" + topLevelAdminDivCodesUserManaged + ") ");
                    recordsExist = sellIntoRateReportHalfBuiltBy(preSql);
                }
                //<-
            }
        }
        return recordsExist;
    }

    private Map<String, SellIntoRate> sellIntoRateReportHalfBuiltBy(String sql) {
        Map<String, SellIntoRate> recordsExist = jdbcTemplate.execute(sql, new PreparedStatementCallback<Map<String, SellIntoRate>>() {
            @Override
            public Map<String, SellIntoRate> doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
                Map<String, SellIntoRate> rtnMap = new LinkedHashMap<String, SellIntoRate>();
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    String areaName = rs.getString(1);
                    String areaCode = rs.getString(2);
                    int plQuantity = rs.getInt(3);
                    int acQuantity = rs.getInt(4);
                    rtnMap.put(areaCode, new SellIntoRate(areaName, areaCode, plQuantity, acQuantity));
                }
                return rtnMap;
            }
        });
        return recordsExist;
    }

    private static <TK extends Serializable> void expandTree4SellInto(AbstractTreeNode<TK> entryNode, List<SellIntoRate> rtnRates, Map<String, SellIntoRate> recordsExist) {
        if (CollectionUtils.isEmpty(entryNode.getChilds())) {
            logger.debug("leaf node:" + entryNode + ",with parent:" + (entryNode.getParent() != null ? entryNode.getParent() : "null"));
            return;//touch leaf node
        } else {
            for (AbstractTreeNode<TK> childNode : entryNode.getChilds()) {
                logger.debug("normal node:" + childNode + ",with parent:" + (childNode.getParent() != null ? childNode.getParent() : "null"));

                SellIntoRate sor = null;
                if ((sor = recordsExist.get(childNode.getId())) != null) {
                    rtnRates.add(sor);
                } else {
                    rtnRates.add(new SellIntoRate(childNode.getName(), (String) childNode.getId(), 0, 0));
                }

                expandTree4SellInto(childNode, rtnRates, recordsExist);
      }
    }
  }

	@Override
	public  List<Map<String,Object>> getPlannedOpenStoresListByCity(User user,int pageSize,String divCode,String projectCode){

		StringBuffer sql=new StringBuffer();

		String baseSql="SELECT CS.id, CS.CHANNEL_CITY_AD_CODE, "+
						     " CS.CHANNEL_CITY_AD_NAME, "+
						     " CS.CHANNEL_AD_CODE, "+
						     " CS.CHANNEL_AD_NAME, "+
						     " CS.channel_address, "+
						     " CS.channel_name, "+
						     " pj.name as project_name "+
						" FROM DM_PROJECT_STORE PS, DM_PROJECT PJ, DM_CHANNEL_SYNC CS "+
					   " WHERE PS.PROJECT = PJ.ID "+
					   "   AND PJ.STATUS = 1 "+
					   "   AND PS.CHANNEL = CS.ID "+
					   "   AND CS.TYPE = 0 ";

		String divCodeSql="and CS.CHANNEL_CITY_AD_CODE = '"+divCode+"' ";

		String projectCodeSql="AND PJ.CODE IN ( $PROJECT_CODES ) ";

		String orderBySql=" order by dbms_random.value ";

		sql.append(baseSql);
		sql.append(divCodeSql);


		if(!StringUtils.isBlank(projectCode)){
			projectCodeSql=projectCodeSql.replaceAll("\\$PROJECT_CODES", "'"+projectCode+"'");
		}else{
			List<Map<String, Object>> projects = projectService.findProjectBasicInfoByUserPermission(user);
			StringBuffer sb=new StringBuffer("");
			if(projects.size()<1){
				projectCodeSql=projectCodeSql.replaceAll("\\$PROJECT_CODES", "");
			}else{
				for (Map<String, Object> map : projects) {
					sb.append(",'"+map.get("code")+"'");
				}
				projectCodeSql=projectCodeSql.replaceAll("\\$PROJECT_CODES",sb.toString().replaceFirst(",", ""));
			}
		}

		sql.append(projectCodeSql);
		sql.append(orderBySql);


		String querySql=new Oracle10gDialect().getLimitString(sql.toString(), false);

		if(pageSize<1){
			pageSize=Integer.MAX_VALUE;	//dangerous
		}


		return jdbcTemplate.queryForList(querySql, new Object[]{pageSize});

	}


	@Override
	public List<Map<String,Object>> findUserLoactions(String dateStart, String dateEnd,String loginName) {
		//重复坐标无法绘制折线图
		String sql="select a.latitude, a.longitude "+
					 " from (select t.latitude, t.longitude, t.created_at "+
					 "  from DM_USER_LOCATION t "+
					 "  where t.created_at between  "+
					 "  to_date('"+dateStart+"', 'yyyy-mm-dd hh24:mi:ss') and "+
					 "  to_date('"+dateEnd+"', 'yyyy-mm-dd hh24:mi:ss') "+
					 "  and t.user_login_name = '"+loginName+"' "+
					 "  order by t.created_at desc) a "+
					 "  group by a.latitude, a.longitude ";

		return jdbcTemplate.queryForList(sql);
	}


	@Override
	public List<SupervisorTaskPerformanceRate> employLastStoreVisitingTrack(final String standardAdminDivAreaCode, Map<String, String> paramMap) {
		
		User user = userService.getUserFromSession();
		
		
		String sql="select JR.EMP_NAME             EXECUTOR_NAME, "+
					     " JR.EMP_ID               EXECUTOR_EMP_ID, "+
					     " JR.PROJ_EMP_TITLE       EXECUTOR_TITLE, "+
					     " CS.CHANNEL_AD_NAME      AD_NAME, "+
					     " CS.CHANNEL_AD_CODE      AD_CODE, "+
					     " cs.channel_address, "+
					     " cs.channel_city_ad_name, "+
					     " jr.job_name, "+
					     " jr.project_name, "+
					     " jr.project_code "+
					" from DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS "+
				   " where JR.id in (SELECT max(a.Id) "+
				   				    "  FROM DM_WF_PLAN_JOB_RESULT a, DM_CHANNEL_SYNC b "+
				   				  "   WHERE b.CHANNEL_CITY_AD_CODE = ?  "+
				   				  	 "  AND a.CHANNEL = b.ID "+
				   				     "  AND (a.AUDIT_STATUS = 0 OR a.AUDIT_STATUS = 2) "+
				   				     "  AND a.JOB_STATUS = 'FINISHED' "+
				   				     "   AND b.TYPE = 0 "+
				   				    "  group by a.emp_id) "+
				   				 "  and JR.channel = CS.id ";
		
		String projectSql=" ";		
		if(!user.getPermissionRole().isAdvanced()){
			projectSql=" AND JR.PROJECT_CODE IN (select t2.code from DM_PROJECT_EMPLOYEE t1,DM_PROJECT t2 where t1.emp_id= "+user.getEmployee().getEmpId()+" and t1.project=t2.id)";
		}
				  
		StringBuffer querySql=new StringBuffer();
		querySql.append(sql)
				.append(projectSql);
		
		
		List<SupervisorTaskPerformanceRate> result = jdbcTemplate.query(querySql.toString(),new Object[]{standardAdminDivAreaCode},new RowMapper<SupervisorTaskPerformanceRate>() {
			@Override
			public SupervisorTaskPerformanceRate mapRow(ResultSet rs, int rowNum)throws SQLException {
				
				SupervisorTaskPerformanceRate stpr=new SupervisorTaskPerformanceRate(rs.getString("EXECUTOR_EMP_ID"),
																					 rs.getString("EXECUTOR_NAME"), 
																				     rs.getString("EXECUTOR_TITLE"), 
																				     rs.getString("AD_NAME"), 
																				     rs.getString("AD_CODE"), 
																				     0, 
																				     0);
		          
		        stpr.setExecuteAddress(rs.getString("channel_address"));
		        stpr.setExecuteJobName(rs.getString("job_name"));
		        stpr.setExecuteProjectName(rs.getString("project_name"));
		        stpr.setExecuteCityName(rs.getString("channel_city_ad_name"));
				
				return stpr;
			}
		});
		
		if(result.size()<1){
			return result;
		}
		
		
		StringBuffer empIds=new StringBuffer();
		
		for (SupervisorTaskPerformanceRate supervisorTaskPerformanceRate : result) {
			empIds.append(",");
			empIds.append("'");
			empIds.append(supervisorTaskPerformanceRate.getExecutorEmpId());
			empIds.append("'");
		}
		
		
		String queryNumSql=" select a.emp_id, PLANNEAD_QUANTITY, ACTUAL_QUANTITY "+
				" from (SELECT emp_id, COUNT(*) PLANNEAD_QUANTITY "+
				" FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS "+
				"  WHERE jr.emp_id in ("+empIds.toString().substring(1)+") "+
				"  and CS.CHANNEL_CITY_AD_CODE = '"+standardAdminDivAreaCode+"' "+
				"  AND JR.CHANNEL = CS.ID "+
				"  group by emp_id) a, "+
				"  (SELECT emp_id, COUNT(*) ACTUAL_QUANTITY "+
				"  FROM DM_WF_PLAN_JOB_RESULT JR, DM_CHANNEL_SYNC CS "+
				"  WHERE jr.emp_id in ("+empIds.toString().substring(1)+") "+
				"  and CS.CHANNEL_CITY_AD_CODE = '"+standardAdminDivAreaCode+"' "+
				"  AND JR.CHANNEL = CS.ID "+
				"  AND (JR.AUDIT_STATUS = 0 OR JR.AUDIT_STATUS = 2) "+
				"  AND JR.JOB_STATUS = 'FINISHED' "+
				"  AND CS.TYPE = 0 "+
				"  GROUP BY JR.EMP_ID) b "+
				"  where a.emp_id = b.emp_id ";
		
		List<Map<String, Object>> nums = jdbcTemplate.queryForList(queryNumSql);
		for (Map<String, Object> map : nums) {
			String empId=map.get("emp_id").toString();
			for (SupervisorTaskPerformanceRate supervisorTaskPerformanceRate : result) {
				if(supervisorTaskPerformanceRate.getExecutorEmpId().equals(empId)){
					supervisorTaskPerformanceRate.setActualQuantityOfTask(Integer.valueOf(map.get("ACTUAL_QUANTITY").toString()));
					supervisorTaskPerformanceRate.setPlannedQuantityOfTask(Integer.valueOf(map.get("PLANNEAD_QUANTITY").toString()));
				}
			}
		}
		
		
		return result;
	}
	
	
	
}
